package com.allen.service;

import com.allen.Utils.JdbcUtils;
import com.allen.bean.Goods;
import com.allen.bean.Outstore;
import com.allen.bean.StoreGoods;
import net.sf.json.JSONArray;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class OutstoreService {

    private Connection connection;
    private Statement statement;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    public String getGoodsJson() {
        List<Goods> goodses = new ArrayList<>();
        String sql = "SELECT * FROM goods;";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Goods goods = new Goods();
                goods.setGid(resultSet.getInt("gid"));
                goods.setGname(resultSet.getString("gname"));
                goods.setMerc(resultSet.getString("merc"));
                goods.setPlace(resultSet.getString("place"));
                goods.setInprice(resultSet.getDouble("inprice"));
                goods.setUnit(resultSet.getString("unit"));
                goodses.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        JSONArray jsonArray = new JSONArray().fromCollection(goodses);
        String json = jsonArray.toString();
        return json;
    }

    public int limit(int gid) {
        int number = 0;
        connection = JdbcUtils.getConnect();
        String sql = "SELECT number FROM stock WHERE gid=?;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, gid);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                number = resultSet.getInt("number");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return number;
    }

    public void updateNum(int num, int gid) {
        String sql = "UPDATE stock SET number=? WHERE gid=?";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, num);
            preparedStatement.setInt(2, gid);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void add(HttpServletRequest req, HttpServletResponse resp) {
        Integer gid = Integer.parseInt(req.getParameter("gid"));
        String gname = req.getParameter("gname");
        String unit = req.getParameter("unit");
        Integer num = Integer.parseInt(req.getParameter("num"));
        Double price = Double.parseDouble(req.getParameter("price"));
        Integer sum = Integer.parseInt(req.getParameter("sum"));
        String no = req.getParameter("no");
        String receiver = req.getParameter("receiver");
        String date = req.getParameter("date");
        String outman = req.getParameter("outman");
        String remark = req.getParameter("remark");
        String count = req.getParameter("count");

        String sql = "insert into outstore (gid,gname,unit,num,price,sum,no,receiver,date,outman,remark,count)" +
                " values(?,?,?,?,?,?,?,?,?,?,?,?)";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, gid);
            preparedStatement.setString(2, gname);
            preparedStatement.setString(3, unit);
            preparedStatement.setInt(4, num);
            preparedStatement.setDouble(5, price);
            preparedStatement.setInt(6, sum);
            preparedStatement.setString(7, no);
            preparedStatement.setString(8, receiver);
            preparedStatement.setString(9, date);
            preparedStatement.setString(10, outman);
            preparedStatement.setString(11, remark);
            preparedStatement.setString(12, count);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List<Outstore> getAllOutstore() {
        List<Outstore> outstoreList = new ArrayList<>();
        String sql = "SELECT no,receiver,date,outman FROM outstore GROUP BY no;";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Outstore outstore = new Outstore();
                outstore.setDate(resultSet.getString("date"));
                outstore.setNo(resultSet.getString("no"));
                outstore.setOutman(resultSet.getString("outman"));
                outstore.setReceiver(resultSet.getString("receiver"));
                outstoreList.add(outstore);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return outstoreList;
    }

    public Outstore detail(HttpServletRequest req, HttpServletResponse resp) {
        Outstore outstore = new Outstore();
        String no = req.getParameter("no");

        String sql = "SELECT no,receiver,date,outman,remark,count FROM outstore WHERE no= ? GROUP BY no;";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, no);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                outstore.setDate(resultSet.getString("date"));
                outstore.setNo(resultSet.getString("no"));
                outstore.setOutman(resultSet.getString("outman"));
                outstore.setReceiver(resultSet.getString("receiver"));
                outstore.setRemark(resultSet.getString("remark"));
                outstore.setCount(resultSet.getString("count"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        String sql2 = "SELECT gname,unit,num,price,sum,gid FROM outstore WHERE no=?";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setString(1,no);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                StoreGoods goods = new StoreGoods();
                goods.setGid(resultSet.getInt("gid"));
                goods.setGname(resultSet.getString("gname"));
                goods.setNum(resultSet.getInt("num"));
                goods.setPrice(resultSet.getDouble("price"));
                goods.setSum(resultSet.getInt("sum"));
                goods.setUnit(resultSet.getString("unit"));
                outstore.getStoreGoods().add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return outstore;
    }
}